package cn.cloudreal.cbms.excel;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import cn.cloudreal.cbms.unit.service.UnitService;
import cn.cloudreal.cbms.vo.Unit;

public class Excel2DB {
	/** 服务 */
	private UnitService unitService;
	
	
    public UnitService getUnitService() {
		return unitService;
	}


	public void setUnitService(UnitService unitService) {
		this.unitService = unitService;
	}


	//处理EXCEL表格数据类型	
	private   String getCellValue(Cell c) {
		String o = null;
		if(c==null)return "";
		switch (c.getCellType()) {
		case Cell.CELL_TYPE_BLANK:
			o = ""; break;
		case Cell.CELL_TYPE_BOOLEAN:
			o = String.valueOf(c.getBooleanCellValue()); break;
		case Cell.CELL_TYPE_FORMULA:
			o = String.valueOf(c.getCellFormula()); break;
		case Cell.CELL_TYPE_NUMERIC:
			o = String.valueOf(c.getNumericCellValue()); break;
		case Cell.CELL_TYPE_STRING:
			o = c.getStringCellValue(); break;
		default:
			o = null;
			break;
		}
		return o;
	}
	
	
	/**
	 * 将excel读到内存中
	 * @param path  excel路径
	 * @param sheetIndex  读excel文件中的第几个sheet
	 * @return
	 */
	public   Sheet readExcelSheet(String path,int sheetIndex) {
		
		try {
			//可以讲上传时候的文件流直接作为参数输入，不用将上传文件保存在项目中
			//Workbook wb = WorkbookFactory.create(new InputStrteam());
			Workbook wb = WorkbookFactory.create(new File(path));
			return wb.getSheetAt(sheetIndex);
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * @param path  excel路径
	 * @param index  从什么地方开始读
	 * @param count  每次读多少行
	 */
	public   void readUnitExcelWhitPage(Sheet sheet,int index,int count) {
		Row row = null;
		
		for(int i=index;i<index+count&&i<sheet.getLastRowNum();i++) {
			row = sheet.getRow(i);
			Unit unit = new Unit();
			unit.setUnitID(getCellValue(row.getCell(0)));
			unit.setUnitName(getCellValue(row.getCell(1)));
			unit.setUnitType(getCellValue(row.getCell(2)));
			unit.setDeviceID(getCellValue(row.getCell(3)));
			unit.setDeviceType(getCellValue(row.getCell(4))); 
			unit.setDeviceSubType(getCellValue(row.getCell(5)));
			unit.setFixMan(getCellValue(row.getCell(6)));
			unit.setFixTime(getCellValue(row.getCell(7)));
			unit.setSupplier(getCellValue(row.getCell(8)));
			unit.setPurchaseDate(getCellValue(row.getCell(9)));
			unit.setStatus(getCellValue(row.getCell(10)));
			unit.setComments(getCellValue(row.getCell(11)));
			unit.setRegisterMan(getCellValue(row.getCell(12)));
			unit.setRegisterTime(getCellValue(row.getCell(13)));
			
			//-----------------------------------------------------------------
			StringBuilder sb = new StringBuilder();
			//获取一行多少列
			for(int j=row.getFirstCellNum();j<row.getLastCellNum();j++) {
				//getCellValue可以根据不同的类型获取一个String类型的值
				sb.append(getCellValue(row.getCell(j))+",");
			}
			sb.deleteCharAt(sb.length()-1);
			System.out.println(sb);
			//----------------------------------------------------------------------
			//调用插入数据库的语句
			unitService.addUnit(unit);
			System.out.println("-----------------unitService.addUnit(unit);-------------------");		
//			System.out.println();
		}
	}
	
  public void insertExcelData2DB(InputStream is){
	  Sheet sheet = readExcelSheet(is,0);
	  readUnitExcelWhitPage(sheet,1,sheet.getLastRowNum());
  }	
  
  
 public   Sheet readExcelSheet(InputStream is,int sheetIndex) {
		
		try {
			//可以讲上传时候的文件流直接作为参数输入，不用将上传文件保存在项目中
			Workbook wb = WorkbookFactory.create(is);
			return wb.getSheetAt(sheetIndex);
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}

	
	//测试
	public static void main(String[] args) {	
		Excel2DB excel2DB = new Excel2DB();
		Sheet sheet = excel2DB.readExcelSheet("F:\\CBMSys\\CBMSys\\WebRoot\\upload\\123.xls",0);
		excel2DB.readUnitExcelWhitPage(sheet,5,5);
	}
}
